Mega Code Archive
Date Timezone
1) Add any number of months to hired date
2) ADD_MONTHS
3) Adding One Month a certain date
4) Adding one month to SYSDATE
5) Adding two months to current date
6) Adding Two Months to SYSDATE
7) Adjust your session time zone to -08
8) ALTER SESSION statement sets the local time zone to Pacific Standard Time (PST)
9) Calculating the Number of Days of Summer in June
10) Calculating the number of days of summer in June, Last_Date function
11) Cast string to TIMESTAMP
12) Change the original date format in the TO_CHAR function
13) Change time zone
14) Combine Months_between, to_date, to_char together
15) Combine to_char and trunc with date value
16) Combine to_char with add_months
17) Combine to_char, add_months and to_date function
18) Combining TO_CHAR to Format SYSDATE
19) Compare data and timestamp
20) Compare the timestamp value between before and after alter session set time_zone
21) Converting a DATE to Another Language
22) Count the months between now and hired date
23) Create a table with two columns
24) CURRENT_DATE
25) CURRENT_TIMESTAMP()
26) Current_timestamp + numtodsinterval( 2, hour )
27) Current_timestamp + numtoyminterval( 3, month ) current_timestamp
28) Date + to_yminterval(01-05)
29) Date value type, value and interval value
30) Default output of SYSDATE
31) Determine the database time zone
32) Determine your session time zone
33) Display the sessions current timetimezone AND the Operating systems timetimezone in PLSQL code block
34) Experimenting with MONTHS_BETWEEN
35) EXTRACT()
36) EXTRACT(DAY FROM TO_DATE(01-JAN-2005 19
37) EXTRACT(MONTH FROM TO_DATE(01-JAN-2005 19
38) EXTRACT(TIMEZONE_ABBR FROM TO_TIMESTAMP_TZ(01-JAN-2005 19
39) EXTRACT(TIMEZONE_HOUR FROM TO_TIMESTAMP_TZ(01-JAN-2005 19
40) EXTRACT(TIMEZONE_MINUTE FROM TO_TIMESTAMP_TZ(01-JAN-2005 19
41) EXTRACT(TIMEZONE_REGION FROM TO_TIMESTAMP_TZ(01-JAN-2005 19
42) Find TZ offset for a given region
43) Finding the First Monday After the Current Date and Time
44) Finding the first Monday in the month of September
45) Finding the last day of the month starting summer
46) Format parameter options available
47) Formats used with the TRUNC() function for the DATE data type
48) FROM_TZ(x, time_zone)
49) Get current operating system date
50) Get database time zone using the DBTIMEZONE() function
51) Get LOCALTIMESTAMP
52) Get NLS date format
53) Get the number of days in a month with to_char
54) Get the SYSTIMESTAMP
55) Get your session time zone using the SESSIONTIMEZONE() function
56) INITCAP(RTRIM(TO_CHAR(SYSDATE, MONTH)))
57) INITCAP(RTRIM(TO_CHAR(SYSDATE, MONTH))) TO_CHAR(SYSDATE, DDTH)
58) Initcap(rtrim(to_char(sysdate, MONTH))) initcap(to_char(sysdate, DDSPTH))
59) Insert a row using the CURRENT_TIMESTAMP function
60) Insert date value along with the default date format
61) INSERT statement adds a row with the TIMESTAMP keyword to supply a datetime literals
62) INTERVAL DAY TO SECOND
63) INTERVAL DAY TO SECOND type to store time intervals measured in days and seconds
64) INTERVAL DAY(3) TO SECOND (4)
65) INTERVAL YEAR(3) TO MONTH
66) LAST_DAY
67) LAST_DAY(hire_date)+1
68) LAST_DAY(x)
69) Minus days from sysdate
70) MONTHS_BETWEEN
71) MONTHS_BETWEEN with to_date function
72) MONTHS_BETWEEN(SYSDATE, last_stock_date)
73) MONTHS_BETWEEN(x, y)
74) NEW_TIME()
75) NEW_TIME() returns the zone_two equivalent of the zone_one date
76) NEXT_DAY
77) NEXT_DAY(01-AUG-97,Monday)
78) NEXT_DAY(SYSDATE,Monday)
79) NLS_DATE_FORMAT
80) NUMTODSINTERVAL(3 25, HOUR)
81) NUMTODSINTERVAL(x, interval_unit)
82) Numtodsinterval(150, DAY)
83) Numtodsinterval(1500, HOUR)
84) Numtodsinterval(15000, MINUTE)
85) Numtodsinterval(150000, SECOND)
86) NUMTOYMINTERVAL(3 25, MONTH)
87) NUMTOYMINTERVAL(x)
88) NUMTOYMINTERVAL(x, interval_unit)
89) Numtoyminterval(100000, YEAR)
90) Obtaining Time Zone Names
91) Obtaining Time Zone Offsets
92) Proper use of default format mask
93) Removing the Time from SYSDATE
94) ROUND
95) ROUND(MONTHS_BETWEEN(SYSDATE, last_stock_date),0)
96) Rounding to the nearest minute
97) Select add_months( sysdate, -12 ) Last Year
98) Select add_months( sysdate, 12 ) Next Year
99) Select current_timestamp from dual to see the session time
100) Select extract( year from add_months(sysdate,36) ) 3 Years Out
101) Select last_day( date2000-02-01 ) Leap Yr
102) Select last_day( sysdate ) Last day of this month
103) Select months_between( sysdate, date1971-05-18 )
104) Select months_between( sysdate, date2001-01-01 )
105) Select next_day( date2001-09-11, SUNDAY )
106) Select next_day( date2002-01-01, FRI )
107) Select round( sysdate )
108) Select round( sysdate, month )
109) Select round( sysdate, q ) Quarter
110) Select round( sysdate, year )
111) Select sysdate
112) Select sysdate - to_dsinterval( 1 00
113) Select the current time zone
114) Select to_char( sysdate, DDMMYY HH24
115) SELECT TO_CHAR(SYSDATE, MM-DD-YYYY HH24
116) Select to_char(sysdate,DD-MON-YY HH24
117) Setting the Default Date Format
118) Simple demo for LAST_DAY function
119) Simple Examples of Storing and Retrieving Dates
120) Start_date+TO_DSINTERVAL(100 10
121) Subtract Month from a given date
122) Syntax
123) The EXTRACT() function returns the value of a specified datetime field from a datetime or interval value expression
124) The proper method to find the first Monday in a given month
125) TIME_ZONE to EST and get CURRENT_TIMESTAMP, LOCALTIMESTAMP, SYSTIMESTAMP
126) TIMESTAMP specifies a precision for the SECONDS field in a TIMESTAMP column (the DATE data type can only store whole seconds
127) TIMESTAMP WITH LOCAL TIME ZONE
128) TIMESTAMP WITH TIME ZONE type extends TIMESTAMP to allow you to store a time zone
129) TIMESTAMP(4) WITH LOCAL TIME ZONE
130) TIMESTAMP(4) WITH TIME ZONE
131) Time Change from Chicago to Los Angeles
132) Time change from Chicago to Los Angeles(Central to Pacific)
133) Time zone name
134) Timestamp with local time zone as a table column
135) To_char( sysdate, HH24
136) TO_CHAR(ADD_MON(TO_DATE(29-FEB-2000,DD-MON-YYYY),1),DD-MON-YYYY)
137) To_char(LAST_STOCK_DATE, MON DD, YYYY HH24
138) To_char(order_date, hh24
139) TO_CHAR(SYSDATE, MONTH DDSP)
140) TO_CHAR(SYSDATE, MONTH DDSPTH)
141) TO_CHAR(SYSDATE, MONTH DDTH)
142) To_date() and insert statement
143) TO_DSINTERVAL(x)
144) TO_TIMESTAMP(x)
145) TO_TIMESTAMP_TZ(2005-05-13 07
146) TO_TIMESTAMP_TZ(x)
147) To_timestamp(2003DEC13 10
148) TO_YMINTERVAL(x)
149) To_yminterval(00-01)
150) To_yminterval(01-05)
151) TRUNC
152) Trunc a date
153) TRUNC()
154) TRUNC(SYSDATE,Q)
155) Truncate and round a date that falls into the second part of a year, the results would be different
156) Truncates 7
157) Truncates May 25, 2005, to the first day in the month
158) Truncating to the Quarter
159) Use date time Interval type
160) Use string returned to_char from date type column in where clause
161) Use sysdate to init a variable
162) Use systimestamp in insert statement
163) Use the DATE keyword to supply a date literal to the database
164) Use the TIMESTAMP type to define a column in a table
165) Use the Timestamp data type in a table and insert data
166) Use TIMESTAMP to mark string in insert statement
167) Use timestamp as table column type and insert sysdate to it
168) Use to_char to format a date type column
169) Use to_date in insert statement
170) Use to_yminterval in PLSQL
171) Use TRUNC(Date value) in where clause
172) Use TRUNC(date_variable) when you want to extract a date from a timestamp
173) Using systimestamp to do benchmark
174) Using the INTERVAL YEAR TO MONTH Type
175) Using the SYSDATE function
176) Using TO_DATE Within PLSQL